package J19;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class J19_Q_Cd implements J19_Q_CdService{
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@127.0.0.1/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";

	Scanner sc = new Scanner(System.in);
	// SQL 문 작성
	private static final String insertSQL 
	 = " INSERT INTO cdinfo(cd_id,title,publisher,artist,price) VALUES(?,?,?,?,?)";
	private static final String selectSQL 
	 = " SELECT * FROM cdinfo ORDER BY cd_id DESC";
	
	

	@Override
	public boolean newCd(J19_Q_CdVO cdv) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName( DRV );
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			// ? 매개변수에 들어갈 실제 값을 지정
				ps.setString(1, cdv.getCd_id());
				ps.setString(2, cdv.getTitle());
				ps.setString(3, cdv.getPublisher());
				ps.setString(4, cdv.getArtist());
				ps.setInt(5, cdv.getPrice());
			
			int cnt = ps.executeUpdate();
			// 성공적으로 insert 되면 result를 true로 
			if (cnt >0) result = true;
		}catch (Exception ex){
			ex.printStackTrace();
		}finally{
			if (ps != null) 
				try{ps.close();}catch(Exception e){}
			if (conn != null) 
				try{conn.close();}catch(Exception e){}
		}
		
		return result;
	}

	@Override
	public String readCd() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			// selectSQL 실행결과를 모두 출력
			while(rs.next()){
				for(int i=1;i <= 5; ++i)
					result += rs.getString(i);
				result += "\n";
			}
		}catch(Exception ex){
		} finally{
			if(rs != null)
				try{rs.close();}catch(Exception e){}
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		
		return result;
	}
}
